set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 400;
set hive.exec.max.dynamic.partitions.pernode = 400;
insert overwrite table jms_dm.dm_free_route_sign_dt partition(dt)
select
    '{{ execution_date | cst_ds }}' as data_time
     ,a.in_from_provider_code
     ,a.in_from_provider_desc
     ,a.in_from_city_code
     ,a.in_from_city_desc
     ,a.in_from_area_code
     ,a.in_from_area_desc
     ,a.in_from_code
     ,a.in_from_name
     ,a.out_to_provider_code
     ,a.out_to_provider_desc
     ,a.out_to_city_code
     ,a.out_to_city_desc
     ,a.out_to_area_code
     ,a.out_to_area_desc
     ,a.out_to_code
     ,a.out_to_name
     ,'00:00:00'
     ,concat(a.taking_end_time,':59')
     ,case when abs(NVL(c.sign_days,0) - NVL(a.search_type,0))<=1 then greatest(cast(NVL(c.sign_days,0) as int),NVL(a.search_type,0))+if(greatest(cast(NVL(c.sign_days,0) as int),NVL(a.search_type,0))=0,1,0)
           when abs(NVL(c.sign_days,0) - NVL(a.search_type,0))>1 then NVL(a.search_type,0)+if(NVL(a.search_type,0)=0,1,0) else a.search_type end as t_use_days
     ,'{{ execution_date | cst_ds }}' as dt
from
    (
        select * from
            (SELECT
                 in_from_provider_code
                  ,in_from_provider_desc
                  ,in_from_city_code
                  ,in_from_city_desc
                  ,in_from_area_code
                  ,in_from_area_desc
                  ,in_from_code
                  ,in_from_name
                  ,out_to_provider_code
                  ,out_to_provider_desc
                  ,out_to_city_code
                  ,out_to_city_desc
                  ,out_to_area_code
                  ,out_to_area_desc
                  ,out_to_code
                  ,out_to_name
                  ,split(latest_warehousing_time_quantum,'-')[1] as taking_end_time
                  ,search_type
                  ,row_number()over(partition by in_from_code,out_to_code order by split(latest_warehousing_time_quantum,'-')[1] desc,search_type desc) rn
             from
                 (select *,
                         row_number()over(partition by in_from_code,out_to_code,in_edge_latest_warehousing order by search_type) rnk
                  from jms_dm.dm_route_lastest_all_dt where dt = '{{ execution_date | cst_ds }}' and is_main_route<>2
                                                        and (in_collect_line_name <>'拉萨集散点-成都1001(2班)' or in_collect_line_name is null)
                                                        and (out_collect_line_name <>'成都-拉萨集散点1230(2班)' or out_collect_line_name is null)
                 )s where s.rnk=1
            ) a where a.rn =1
    ) a
        left join
    (
        select * from
            (select
                 pick_network_code
                  ,dispatch_network_code
                  ,sign_days
                  ,waybill_count
                  ,row_number()over(partition by pick_network_code,dispatch_network_code order by waybill_count desc) rn
             from
                 (
                     select
                         pick_network_code
                          ,dispatch_network_code
                          ,sign_days
                          ,sum(waybill_count) as waybill_count
                     from jms_tmp.tmp_waybill_sign_taking_time_quantum_dt where dt ='{{ execution_date | cst_ds }}'
                     group BY
                         pick_network_code
                            ,dispatch_network_code
                            ,sign_days
                 )a
            )b where b.rn =1

    ) c
    on a.in_from_code = c.pick_network_code and a.out_to_code = c.dispatch_network_code

union ALL

select
    '{{ execution_date | cst_ds }}' as data_time
     ,a.in_from_provider_code
     ,a.in_from_provider_desc
     ,a.in_from_city_code
     ,a.in_from_city_desc
     ,a.in_from_area_code
     ,a.in_from_area_desc
     ,a.in_from_code
     ,a.in_from_name
     ,a.out_to_provider_code
     ,a.out_to_provider_desc
     ,a.out_to_city_code
     ,a.out_to_city_desc
     ,a.out_to_area_code
     ,a.out_to_area_desc
     ,a.out_to_code
     ,a.out_to_name
     ,from_unixtime(unix_timestamp(concat('2099-01-01 ',taking_end_time,':59'))+1,'HH:mm:ss')
     ,'23:59:59'
     ,case when abs(NVL(c.sign_days,0) - NVL(a.search_type,0))<=1 then greatest(cast(NVL(c.sign_days,0) as int),NVL(a.search_type,0))+1+if(greatest(cast(NVL(c.sign_days,0) as int),NVL(a.search_type,0))=0,1,0)
           when abs(NVL(c.sign_days,0) - NVL(a.search_type,0))>1 then a.search_type+1+if(a.search_type=0,1,0) else a.search_type+1 end as t_use_days
     ,'{{ execution_date | cst_ds }}' as dt
from
    (
        select *
        from
            (SELECT
                 in_from_provider_code
                  ,in_from_provider_desc
                  ,in_from_city_code
                  ,in_from_city_desc
                  ,in_from_area_code
                  ,in_from_area_desc
                  ,in_from_code
                  ,in_from_name
                  ,out_to_provider_code
                  ,out_to_provider_desc
                  ,out_to_city_code
                  ,out_to_city_desc
                  ,out_to_area_code
                  ,out_to_area_desc
                  ,out_to_code
                  ,out_to_name
                  ,split(latest_warehousing_time_quantum,'-')[1] as taking_end_time
                  ,search_type
                  ,row_number()over(partition by in_from_code,out_to_code order by split(latest_warehousing_time_quantum,'-')[1] desc,search_type desc) rn
             from
                 (select *,
                         row_number()over(partition by in_from_code,out_to_code,in_edge_latest_warehousing order by search_type) rnk
                  from jms_dm.dm_route_lastest_all_dt
                  where dt = '{{ execution_date | cst_ds }}' and is_main_route<>2
                    and (in_collect_line_name <>'拉萨集散点-成都1001(2班)' or in_collect_line_name is null)
                    and (out_collect_line_name <>'成都-拉萨集散点1230(2班)' or out_collect_line_name is null)
                 )s where s.rnk=1
            ) a where a.rn =1
    ) a
        left join
    (
        select * from
            (select
                 pick_network_code
                  ,dispatch_network_code
                  ,sign_days
                  ,waybill_count
                  ,row_number()over(partition by pick_network_code,dispatch_network_code order by waybill_count desc) rn
             from
                 (
                     select
                         pick_network_code
                          ,dispatch_network_code
                          ,sign_days
                          ,sum(waybill_count) as waybill_count
                     from jms_tmp.tmp_waybill_sign_taking_time_quantum_dt  where dt ='{{ execution_date | cst_ds }}'
                     group BY
                         pick_network_code
                            ,dispatch_network_code
                            ,sign_days
                 )a
            )b where b.rn =1
    ) c
    on a.in_from_code = c.pick_network_code and a.out_to_code = c.dispatch_network_code
where a.taking_end_time <> '23:59';

insert overwrite table jms_tmp.tmp_waybill_sign_taking_time_quantum_dt partition(dt)
select
    pick_network_code
     ,pick_network_name
     ,dispatch_network_code
     ,dispatch_network_name
     ,latest_warehousing_time_quantum
     ,sign_days
     ,waybill_count
     ,if(in_from_code is not null and out_to_code is not null,1,0)
     ,''
     ,''
     ,''
     ,aaa.dt
from jms_tmp.tmp_waybill_sign_taking_time_quantum_dt aaa
         left join (select in_from_code,out_to_code from jms_dm.dm_free_route_sign_dt where dt = '{{ execution_date | cst_ds }}' group by in_from_code,out_to_code) bbb
                   on aaa.pick_network_code = bbb.in_from_code and aaa.dispatch_network_code = bbb.out_to_code
where aaa.dt ='{{ execution_date | cst_ds }}';